package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.edu.bean.Comment;
import com.edu.bean.Upkeep;
import com.edu.dao.BaseDao;
import com.edu.dao.CommentDao;

public class CommentDaoImpl implements CommentDao {

	@Override
	public List<Comment> selectCommentAll(int pageNum, int pageSize) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Comment> Commnet = new ArrayList();
		String sql = "SELECT * FROM comment LIMIT ?,?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, (pageNum-1)*pageSize);
			ps.setInt(2, pageSize);
			res = ps.executeQuery();
			while(res.next()) {
				Comment comment = new Comment();
				comment.setCommentid(res.getInt("commentid"));
				comment.setUserid(res.getInt("userid"));
				comment.setCarid(res.getInt("carid"));
				comment.setCommentString(res.getString("commentString"));
				comment.setCommetnTime(res.getDate("commetnTime"));
				Commnet.add(comment);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return Commnet;
	}

	@Override
	public int selectCommenttotal(Comment comment) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT count(1) as total FROM comment WHERE 1=1 ";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int deleteCommentById(int Commentid) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "DELETE FROM comment WHERE commentId = ?";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, Commentid);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		
		return result;
	}

	@Override
	public int updateComment(Comment comment) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE comment set userId = ?,carId = ?,commentString = ?,commetnTime = ? WHERE commentId = ? ";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, comment.getUserid());
			ps.setInt(2, comment.getCarid());
			ps.setString(3, comment.getCommentString());
			ps.setDate(4, new Date(comment.getCommetnTime().getTime()));
			ps.setInt(5, comment.getCommentid());
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public Comment selectCommentById(int Commentid) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		Comment comment=null;
		String sql = "SELECT * FROM comment WHERE commentId = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,Commentid);
			res = ps.executeQuery();
			if(res.next()) {
				comment = new Comment();
				comment.setCommentid(res.getInt("commentid"));
				comment.setUserid(res.getInt("userid"));
				comment.setCarid(res.getInt("carid"));
				comment.setCommentString(res.getString("commentString"));
				comment.setCommetnTime(res.getDate("commetnTime"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return comment;
	}

	@Override
	public int addComment(Comment comment) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "INSERT INTO comment(userId,carId,commentString,commetnTime) VALUES (?,?,?,?)";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, comment.getUserid());
			ps.setInt(2, comment.getCarid());
			ps.setString(3, comment.getCommentString());
			ps.setDate(4, new Date(comment.getCommetnTime().getTime()));
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

}
